import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from typing import List, Optional, Dict, Any
import logging
from logging.handlers import TimedRotatingFileHandler
from datetime import datetime
import os

# 配置日志
LOG_DIR = "logs"
os.makedirs(LOG_DIR, exist_ok=True)

log_file = os.path.join(LOG_DIR, "recommend.log")
handler = TimedRotatingFileHandler(
    log_file, when="midnight", interval=1, backupCount=7, encoding="utf-8"
)
formatter = logging.Formatter(
    "%(asctime)s - %(levelname)s - %(message)s", datefmt="%Y-%m-%d %H:%M:%S"
)
handler.setFormatter(formatter)

logger = logging.getLogger("recommend_logger")
logger.setLevel(logging.INFO)
logger.addHandler(handler)

console_handler = logging.StreamHandler()
console_handler.setFormatter(formatter)
logger.addHandler(console_handler)

app = FastAPI(title="视频推荐API服务", description="基于用户标签偏好的视频推荐系统，排除已观看视频")

DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'videos_all'
}

class DataLoader:
    def __init__(self):
        self.video_df = None
        self.user_df = None
        self.member_df = None  # 用户性别数据
        self.ks = [0.8, 1.2, 0.2, 1.5, 1, 1.3, 1.4, 1.1, 0.6, 0.7]
        self.engine = None
        self.user_id_field = "user_id"  # 默认用户ID字段名
        
    def create_connection(self):
        try:
            connection_string = f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}"
            self.engine = create_engine(connection_string)
            logger.info("数据库连接成功")
            return True
        except Exception as e:
            logger.error(f"数据库连接失败: {str(e)}")
            return False

    def load_member_data(self):
        """加载用户性别数据"""
        try:
            member_query = "SELECT * FROM t_member"
            self.member_df = pd.read_sql(member_query, self.engine)
            logger.info(f'成功从数据库加载用户性别数据: {len(self.member_df)}条')
            
            # 自动匹配用户ID字段
            if self.user_id_field not in self.member_df.columns:
                possible_fields = ["user_id", "uid", "userid", "id"]
                found = False
                for field in possible_fields:
                    if field in self.member_df.columns:
                        # 临时重命名以统一字段名
                        self.member_df = self.member_df.rename(columns={field: self.user_id_field})
                        found = True
                        logger.warning(f"用户性别表中未找到'user_id'字段，使用'{field}'作为用户ID字段")
                        break
                if not found:
                    logger.error("用户性别表中未找到用户ID相关字段")
                    return False
            return True
        except Exception as e:
            logger.error(f"从数据库加载用户性别数据失败: {str(e)}")
            return False

    def load_data(self):
        if not self.create_connection():
            return False
            
        try:
            # 加载视频数据
            video_query = "SELECT * FROM videos"
            self.video_df = pd.read_sql(video_query, self.engine)
            logger.info(f'成功从数据库加载视频数据: {len(self.video_df)}条')
            
            # 加载用户画像数据
            user_query = "SELECT * FROM user_portraits"
            self.user_df = pd.read_sql(user_query, self.engine)
            logger.info(f'成功从数据库加载用户画像数据: {len(self.user_df)}条')
            
            # 自动匹配用户ID字段（兼容不同命名）
            if self.user_id_field not in self.user_df.columns:
                possible_fields = ["user_id", "uid", "userid"]
                found = False
                for field in possible_fields:
                    if field in self.user_df.columns:
                        self.user_id_field = field
                        found = True
                        logger.warning(f"用户画像表中未找到'user_id'字段，使用'{field}'作为用户ID字段")
                        break
                if not found:
                    logger.error("用户画像表中未找到用户ID相关字段")
                    return False
            
            # 加载用户性别数据
            if not self.load_member_data():
                logger.warning("无法加载用户性别数据，性别相关的标签权重调整功能将不可用")
            
            return True
        except Exception as e:
            logger.error(f"从数据库加载数据失败: {str(e)}")
            return False
    
    def get_user_watched_videos(self, user_id):
        """实时获取用户观看记录"""
        try:
            # 构建查询SQL
            query = f"SELECT vid FROM viewlogs WHERE {self.user_id_field} = '{user_id}'"
            watched_df = pd.read_sql(query, self.engine)
            watched_videos = watched_df['vid'].unique().tolist()
            logger.info(f'实时查询用户{user_id}的观看记录，共{len(watched_videos)}条')
            return watched_videos
        except Exception as e:
            logger.error(f"查询用户{user_id}观看记录失败: {str(e)}")
            return []

    def calculate_tag_weight(self, row):
        """计算用户标签偏好权重"""
        try:
            # 提取用户行为特征（播放率、完成率、点赞率、收藏率、分享率）
            features = [
                row.get('play_rate', 0),
                row.get('completions', 0) / max(row.get('play_videos', 1), 1),  # 完成率（避免除零）
                row.get('likes', 0) / max(row.get('play_videos', 1), 1),       # 点赞率
                row.get('favourites', 0) / max(row.get('play_videos', 1), 1),  # 收藏率
                row.get('shares', 0) / max(row.get('play_videos', 1), 1)       # 分享率
            ]
            # 特征值归一化到[0,1]区间
            features = [min(max(f, 0), 1) for f in features]
            # 加权求和计算最终权重
            return sum([self.ks[i] * features[i] for i in range(min(len(self.ks), len(features)))])
        except Exception as e:
            logger.error(f"计算用户标签权重时出错: {e}, 使用默认值0")
            return 0

    def calculate_time_decay(self, publish_time: str, decay_rate: float = 0.01):
        """计算视频时间衰减因子（越新的视频权重越高）"""
        try:
            # 处理Timestamp类型的发布时间
            if isinstance(publish_time, pd.Timestamp):
                publish_time = publish_time.strftime("%Y-%m-%d %H:%M:%S")
        
            # 解析发布时间并计算天数差
            publish_datetime = datetime.strptime(publish_time, "%Y-%m-%d %H:%M:%S")
            time_diff = datetime.now() - publish_datetime
            days_diff = time_diff.days
            # 指数衰减公式（decay_rate=0.01时，30天前的视频衰减到0.74，100天前衰减到0.37）
            decay_factor = np.exp(-decay_rate * days_diff)
            return decay_factor
        except Exception as e:
            logger.error(f"计算视频时间衰减时出错: {e}, 使用默认值1.0（不衰减）")
            return 1.0

    def get_user_sex(self, user_id):
        """获取用户性别"""
        if self.member_df is None or self.member_df.empty:
            return None
            
        try:
            user_sex_data = self.member_df[self.member_df[self.user_id_field] == user_id]
            if len(user_sex_data) == 0:
                return None
                
            sex = user_sex_data.iloc[0].get('sex')
            # 处理不同的性别表示方式
            if sex in ['男', '男性', 'male', 'Male', 'M', 'm']:
                return '男'
            elif sex in ['女', '女性', 'female', 'Female', 'F', 'f']:
                return '女'
            else:
                return None
        except Exception as e:
            logger.error(f"获取用户{user_id}性别时出错: {str(e)}")
            return None


# 初始化数据加载器并加载数据
data_loader = DataLoader()
if not data_loader.load_data():
    logger.error("无法加载核心数据，服务可能无法正常工作")


# ------------------------------
# Pydantic 数据模型（API请求/响应格式）
# ------------------------------
class TagAdjustmentInfo(BaseModel):
    """标签调整信息模型"""
    original: float
    adjusted: float
    boost_factor: float

class RecommendationRequest(BaseModel):
    user_id: str               # 目标用户ID
    top_n: Optional[int] = 20  # 固定返回20个推荐视频
    temperature: Optional[float] = 2.0  # 推荐多样性调节参数（值越大多样性越高）
    gender_boost_factor: Optional[float] = 1.5  # 性别相关标签权重加强系数

class SimpleRecommendationResponse(BaseModel):
    """简化响应格式"""
    code: int
    result: List[str]
    msg: str


# ------------------------------
# 核心工具函数
# ------------------------------
def adjusted_softmax(scores, temperature=1.0):
    """带温度调节的Softmax函数（用于归一化用户偏好权重，控制多样性）"""
    if len(scores) == 0:
        return []
    
    scores = np.array(scores)
    # 标准化分数（消除量纲影响）
    mean = np.mean(scores)
    std = np.std(scores) if np.std(scores) > 0 else 1  # 避免标准差为0导致除零
    normalized_scores = (scores - mean) / std
    
    # 温度调节（temperature>1增加多样性，<1增强确定性）
    exp_scores = np.exp(normalized_scores / temperature)
    return exp_scores / np.sum(exp_scores)  # 返回归一化后的概率分布

def get_hot_videos(top_n: int = 20, watched_videos: List[str] = None):
    """
    获取热门视频（按 likes + favorites + reposts 总和排序）
    :param top_n: 推荐视频数量
    :param watched_videos: 用户已观看视频列表
    :return: 排序后的热门视频DataFrame
    """
    if watched_videos is None:
        watched_videos = []
        
    # 复制视频数据避免修改原数据
    dfv = data_loader.video_df.copy()
    
    # 1. 计算视频互动总和（处理缺失值为0）
    required_fields = ["likes", "favorites", "reposts"]
    for field in required_fields:
        if field not in dfv.columns:
            logger.warning(f"视频表缺少字段'{field}'，按0值处理")
            dfv[field] = 0  # 缺失字段填充为0
    
    dfv["sum_interactions"] = (
        dfv["likes"].fillna(0) + 
        dfv["favorites"].fillna(0) + 
        dfv["reposts"].fillna(0)
    )
    
    # 2. 排除用户已观看视频
    if watched_videos:
        dfv = dfv[~dfv["vid"].isin(watched_videos)]
        if len(dfv) == 0:
            logger.warning("用户已观看所有视频，返回空热门推荐结果")
            return dfv
    
    # 3. 按互动总和降序排序，取前top_n条
    dfv_sorted = dfv.sort_values(by="sum_interactions", ascending=False).reset_index(drop=True)
    actual_top_n = min(top_n, len(dfv_sorted))  # 避免视频总数不足top_n
    return dfv_sorted.head(actual_top_n)


# ------------------------------
# 推荐核心逻辑
# ------------------------------
def get_user_recommendations(user_id, top_n=20, temperature=2.0, gender_boost_factor=1.5):
    """
    获取用户个性化推荐（用户不存在时返回热门视频）
    :param user_id: 目标用户ID
    :param top_n: 推荐视频数量
    :param temperature: 推荐多样性调节参数
    :param gender_boost_factor: 性别相关标签权重加强系数
    :return: 推荐结果字典（含用户偏好标签、排序后视频等）
    """
    # 1. 实时获取用户观看记录
    watched_videos = data_loader.get_user_watched_videos(user_id)
    logger.info(f"用户{user_id}已观看视频数量: {len(watched_videos)}")
    
    # 2. 获取用户性别
    user_sex = data_loader.get_user_sex(user_id)
    logger.info(f"用户{user_id}的性别为: {user_sex}")
    
    # 3. 查找目标用户的画像数据
    user_mask = data_loader.user_df[data_loader.user_id_field] == user_id
    dfu = data_loader.user_df[user_mask].copy()
    
    # 4. 处理用户不存在的情况：返回热门视频
    if len(dfu) == 0:
        logger.info(f"用户{user_id}不存在，自动返回热门视频推荐（按likes+favourites+reposts排序）")
        hot_videos = get_hot_videos(top_n=top_n, watched_videos=watched_videos)
        return {
            "user_prefer_tags": {},  # 无用户偏好标签
            "sorted_prefer_tags": [],
            "selected_videos": hot_videos,
            "user_sex": user_sex,
            "adjusted_tags": {},  # 无调整标签
            "watched_count": len(watched_videos)  # 已观看视频数量
        }
    
    # 5. 处理用户存在的情况：基于标签偏好推荐
    # 5.1 提取用户标签（优先用labels字段，缺失时用category字段）
    dfu["user_tag"] = dfu["labels"].fillna(dfu["category"])
    # 5.2 计算用户标签偏好权重
    dfu["prefer_weight"] = dfu.apply(data_loader.calculate_tag_weight, axis=1)
    
    # 5.3 合并相同标签的权重（保留最高权重）
    user_prefer_tags = {}
    for _, row in dfu.iterrows():
        if pd.notna(row["user_tag"]):
            tag = row["user_tag"].strip()
            weight = row["prefer_weight"]
            # 若标签已存在，保留权重更高的记录
            if tag not in user_prefer_tags or weight > user_prefer_tags[tag]:
                user_prefer_tags[tag] = weight
    
    # 5.4 根据性别调整特定标签权重
    adjusted_tags_info = {}  # 记录调整过的标签
    if user_sex and user_prefer_tags:
        if user_sex == '男':
            # 男性用户：加强"美女"相关标签权重
            beauty_tags = ['美女', '女神', '美女主播', '美女视频', '漂亮', '美女舞蹈']
            for tag in beauty_tags:
                if tag in user_prefer_tags:
                    original_weight = user_prefer_tags[tag]
                    user_prefer_tags[tag] *= gender_boost_factor
                    adjusted_tags_info[tag] = TagAdjustmentInfo(
                        original=original_weight,
                        adjusted=user_prefer_tags[tag],
                        boost_factor=gender_boost_factor
                    )
                    logger.info(f"男性用户{user_id}: 标签'{tag}'权重从{original_weight:.3f}调整到{user_prefer_tags[tag]:.3f}")
        
        elif user_sex == '女':
            # 女性用户：加强"帅哥"相关标签权重
            handsome_tags = ['帅哥', '男神', '帅哥主播', '帅哥视频', '帅气', '帅哥舞蹈']
            for tag in handsome_tags:
                if tag in user_prefer_tags:
                    original_weight = user_prefer_tags[tag]
                    user_prefer_tags[tag] *= gender_boost_factor
                    adjusted_tags_info[tag] = TagAdjustmentInfo(
                        original=original_weight,
                        adjusted=user_prefer_tags[tag],
                        boost_factor=gender_boost_factor
                    )
                    logger.info(f"女性用户{user_id}: 标签'{tag}'权重从{original_weight:.3f}调整到{user_prefer_tags[tag]:.3f}")
    
    # 5.5 归一化标签权重（带多样性调节）
    if user_prefer_tags:
        tag_list = list(user_prefer_tags.keys())
        weights = np.array([user_prefer_tags[tag] for tag in tag_list])
        normalized_weights = adjusted_softmax(weights, temperature)
        user_prefer_tags = {tag: normalized_weights[i] for i, tag in enumerate(tag_list)}
    
    # 5.6 匹配视频标签与用户偏好
    dfv = data_loader.video_df.copy()
    
    # 5.7 排除已观看视频
    if watched_videos:
        dfv = dfv[~dfv["vid"].isin(watched_videos)]
        if len(dfv) == 0:
            logger.warning(f"用户{user_id}已观看所有视频，返回空推荐结果")
            return {
                "user_prefer_tags": user_prefer_tags,
                "sorted_prefer_tags": sorted(user_prefer_tags.items(), key=lambda x: x[1], reverse=True)[:10],
                "selected_videos": pd.DataFrame(),
                "user_sex": user_sex,
                "adjusted_tags": adjusted_tags_info,
                "watched_count": len(watched_videos)
            }
    
    # 5.8 解析视频标签（按分号分隔，去重）
    dfv["video_tags_list"] = dfv["labels"].apply(
        lambda x: list(set([tag.strip() for tag in str(x).split(';')])) if pd.notna(x) else []
    )
    
    # 5.9 计算视频匹配得分（标签匹配度 × 时间衰减因子）
    def calculate_video_score(row):
        # 标签匹配得分：用户偏好标签与视频标签的权重总和
        tag_score = sum([user_prefer_tags.get(tag, 0) for tag in row["video_tags_list"]])
        # 时间衰减因子：越新的视频得分越高
        time_decay = data_loader.calculate_time_decay(row["publishtime"])
        return tag_score * time_decay
    
    dfv["match_score"] = dfv.apply(calculate_video_score, axis=1)
    
    # 5.10 按匹配得分降序排序，取前top_n条
    dfv_sorted = dfv.sort_values(by="match_score", ascending=False).reset_index(drop=True)
    actual_top_n = min(top_n, len(dfv_sorted))
    dfv_selected = dfv_sorted.head(actual_top_n)
    
    # 5.11 整理用户偏好标签（取前10个权重最高的标签）
    sorted_prefer_tags = sorted(user_prefer_tags.items(), key=lambda x: x[1], reverse=True)[:10]
    
    return {
        "user_prefer_tags": user_prefer_tags,
        "sorted_prefer_tags": sorted_prefer_tags,
        "selected_videos": dfv_selected,
        "user_sex": user_sex,
        "adjusted_tags": adjusted_tags_info,
        "watched_count": len(watched_videos)
    }


# ------------------------------
# FastAPI 接口定义
# ------------------------------
@app.get("/")
async def root():
    return {"message": "视频推荐API服务（支持个性化推荐/热门视频推荐）", "status": "running"}

@app.post("/recommend", response_model=SimpleRecommendationResponse)
async def recommend_videos(request: RecommendationRequest):
    """
    单用户推荐接口
    - 若用户存在：返回基于标签偏好的个性化推荐（实时排除已观看）
    - 若用户不存在：返回按 likes+favourites+reposts 排序的热门视频（实时排除已观看）
    - 固定返回20个视频ID
    """
    try:
        # 调用核心推荐逻辑
        result = get_user_recommendations(
            user_id=request.user_id,
            top_n=20,  # 固定返回20个视频
            temperature=request.temperature,
            gender_boost_factor=request.gender_boost_factor
        )
        
        # 提取视频ID列表
        video_ids = []
        if not result["selected_videos"].empty:
            video_ids = [str(row["vid"]) for _, row in result["selected_videos"].iterrows()]
        
        return SimpleRecommendationResponse(
            code=200,
            result=video_ids,
            msg=f"success (已排除{result.get('watched_count', 0)}个已观看视频)"
        )
    except Exception as e:
        logger.error(f"为用户{request.user_id}生成推荐时出错: {str(e)}")
        return SimpleRecommendationResponse(
            code=500,
            result=[],
            msg=f"推荐服务异常: {str(e)}"
        )

@app.get("/recommend/all")
async def recommend_all_users(top_n: int = 20, gender_boost_factor: float = 1.5):
    """批量为所有用户生成推荐（用户不存在时自动跳过）"""
    try:
        # 获取所有有效用户ID
        user_ids = data_loader.user_df[data_loader.user_id_field].unique()
        results = {}
        processed_count = 0
        
        for user_id in user_ids:
            try:
                # 为单个用户生成推荐
                user_result = get_user_recommendations(
                    user_id=user_id, 
                    top_n=top_n, 
                    gender_boost_factor=gender_boost_factor
                )
                # 提取视频ID列表
                video_ids = []
                if not user_result["selected_videos"].empty:
                    video_ids = [str(row["vid"]) for _, row in user_result["selected_videos"].iterrows()]
                
                results[user_id] = {
                    "code": 200,
                    "result": video_ids,
                    "msg": f"success (已排除{user_result.get('watched_count', 0)}个已观看视频)"
                }
                processed_count += 1
            except Exception as e:
                logger.error(f"处理用户{user_id}时出错: {str(e)}，已跳过")
                results[user_id] = {
                    "code": 500,
                    "result": [],
                    "msg": f"处理用户时出错: {str(e)}"
                }
                continue
        
        return {
            "processed_users": processed_count,
            "results": results,
            "timestamp": datetime.now().isoformat()
        }
    except Exception as e:
        logger.error(f"批量推荐时出错: {str(e)}")
        return {
            "code": 500,
            "result": {},
            "msg": f"批量推荐服务异常: {str(e)}"
        }

if __name__ == "__main__":
    import uvicorn
    # 启动服务（默认端口8102，允许外部访问）
    uvicorn.run(app, host="0.0.0.0", port=8102, log_level="info")